package com.winning.sx.microframework.common.util;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.util.ResourceUtils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * sqlLite帮助类，直接创建该类示例，并调用相应的借口即可对sqlLite数据库进行操作
 * Created by Kong on 2019-02-22.
 * 执行完sql，记得调用destroyed回收方法，否则SqlLite会lock
 */
public class SqlLiteHelper {
	private final static Logger logger = LoggerFactory.getLogger(SqlLiteHelper.class);

	private Connection connection;
	private Statement statement;
	private ResultSet resultSet;
	private String dbFilePath;

	public SqlLiteHelper() throws ClassNotFoundException, SQLException {
		try {
//			this.dbFilePath = ResourceUtils.getURL("classpath:").getPath().concat("webFilter.db");
			//2019-03-22 @Kong 更换为运行jar文件的目录，调试在项目根目录下
			this.dbFilePath = ResourceUtils.getURL("").getPath().concat("webFilter.db");
		} catch (Exception e) {
			e.printStackTrace();
			this.dbFilePath = "D:/webFilter.db";
		}
		getStatement().executeUpdate("CREATE TABLE IF NOT EXISTS users(code TEXT NOT NULL UNIQUE, password TEXT NOT NULL ,valid INT NOT NULL, des TEXT, in_time TIMESTAMP DEFAULT (datetime('now', 'localtime')))");
		getStatement().executeUpdate("CREATE TABLE IF NOT EXISTS roles(name TEXT NOT NULL UNIQUE, valid INT NOT NULL,des TEXT, in_time TIMESTAMP DEFAULT (datetime('now', 'localtime')))");
		getStatement().executeUpdate("CREATE TABLE IF NOT EXISTS user_role(uid INT NOT NULL, rid INT NOT NULL, in_time TIMESTAMP DEFAULT (datetime('now', 'localtime')))");
		getStatement().executeUpdate("CREATE TABLE IF NOT EXISTS res(app TEXT NOT NULL, controller TEXT, method TEXT, valid INT NOT NULL, des TEXT, in_time TIMESTAMP DEFAULT (datetime('now', 'localtime')))");
		getStatement().executeUpdate("CREATE TABLE IF NOT EXISTS role_res(rid INT NOT NULL, res_id INT NOT NULL, in_time TIMESTAMP DEFAULT (datetime('now', 'localtime')))");
	}

	public boolean checkUserSecret(String uid, String appSecret) {
		try {
			ResultSet rs = getStatement().executeQuery("SELECT rowid FROM users where code='" + uid + "' and password='" + appSecret + "' ");
			while (rs.next()) {
				int rowId = rs.getInt("rowid");
				if (rowId > 0)
					return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			destroyed();
		}
		return false;
	}



	/**
	 * 查询code对应的有效url集合
	 *
	 * @param uid code
	 */
	public List<String> queryUrls(String uid) throws Exception {
		List<String> list = new ArrayList<String>();
		try {
			ResultSet rs = getStatement().executeQuery(" SELECT res.controller,res.method FROM user_role a " +
					" INNER JOIN roles b ON a.rid=b.rowid " +
					" INNER JOIN role_res c ON c.rid=b.rowid " +
					" INNER JOIN res ON res.rowid = c.res_id " +
					" INNER JOIN users u ON u.rowid = a.uid " +
					" WHERE u.code='" + uid + "' " +
					" AND u.valid=1 " +
					" AND b.valid=1 " +
					" AND res.valid=1 ");
			while (rs.next()) {
				list.add("/".concat(rs.getString("controller")).concat("/").concat(rs.getString("method")));
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} finally {
			destroyed();
		}
		return list;
	}



	public void delUserRole(String uid) throws Exception {
		try {
			this.executeUpdate("DELETE FROM user_role where uid=" + uid);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} finally {
			destroyed();
		}
	}

	public void addUserRole(String uid, String roleId) throws Exception {
		try {
			getStatement().executeUpdate("INSERT INTO user_role(uid,rid) VALUES (" + uid + "," + roleId + ")");
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} finally {
			destroyed();
		}
	}

	public void delRoleRes(String roleId) throws Exception {
		try {
			this.executeUpdate("DELETE FROM role_res where rid=" + roleId);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} finally {
			destroyed();
		}
	}

	public void addRoleRes(String roleId, String res_id) throws Exception {
		try {
			getStatement().executeUpdate("INSERT INTO role_res(rid, res_id) VALUES (" + roleId + "," + res_id + ")");
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} finally {
			destroyed();
		}
	}

	/**
	 * 获取数据库连接
	 *
	 * @param dbFilePath db文件路径
	 * @return 数据库连接
	 */
	private Connection getConnection(String dbFilePath) throws ClassNotFoundException, SQLException {
		Connection conn = null;
		Class.forName("org.sqlite.JDBC");
		conn = DriverManager.getConnection("jdbc:sqlite:" + dbFilePath);
		return conn;
	}

	/**
	 * 执行sql查询
	 *
	 * @param sql sql select 语句
	 * @param rse 结果集处理类对象
	 * @return 查询结果
	 */
	private <T> T executeQuery(String sql, ResultSetExtractor<T> rse) throws SQLException, ClassNotFoundException {
		try {
			resultSet = getStatement().executeQuery(sql);
			T rs = rse.extractData(resultSet);
			return rs;
		} finally {
			destroyed();
		}
	}

	/**
	 * 执行select查询，返回结果列表
	 *
	 * @param sql sql select 语句
	 * @param rm  结果集的行数据处理类对象
	 */
	private <T> List<T> executeQuery(String sql, RowMapper<T> rm) throws SQLException, ClassNotFoundException {
		List<T> rsList = new ArrayList<T>();
		try {
			resultSet = getStatement().executeQuery(sql);
			while (resultSet.next()) {
				rsList.add(rm.mapRow(resultSet, resultSet.getRow()));
			}
		} finally {
			destroyed();
		}
		return rsList;
	}

	/**
	 * 执行数据库更新sql语句
	 *
	 * @return 更新行数
	 */
	private int executeUpdate(String sql) throws SQLException, ClassNotFoundException {
		try {
			return getStatement().executeUpdate(sql);
		} finally {
			destroyed();
		}

	}

	/**
	 * 执行多个sql更新语句
	 */
	private void executeUpdate(String... sqls) throws SQLException, ClassNotFoundException {
		try {
			for (String sql : sqls) {
				getStatement().executeUpdate(sql);
			}
		} finally {
			destroyed();
		}
	}

	/**
	 * 执行数据库更新 sql List
	 *
	 * @param sqls sql列表
	 */
	private void executeUpdate(List<String> sqls) throws SQLException, ClassNotFoundException {
		try {
			for (String sql : sqls) {
				getStatement().executeUpdate(sql);
			}
		} finally {
			destroyed();
		}
	}

	private Connection getConnection() throws ClassNotFoundException, SQLException {
		if (null == connection) connection = getConnection(dbFilePath);
		return connection;
	}

	private Statement getStatement() throws SQLException, ClassNotFoundException {
		if (null == statement) statement = getConnection().createStatement();
		return statement;
	}

	/**
	 * 数据库资源关闭和释放
	 */
	private void destroyed() {
		try {
			if (null != resultSet) {
				resultSet.close();
				resultSet = null;
			}
			if (null != statement) {
				statement.close();
				statement = null;
			}
			if (null != connection) {
				connection.close();
				connection = null;
			}
		} catch (SQLException e) {
			logger.error("Sqlite数据库关闭时异常", e);
		}
	}
}